package helper;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

public class Extractor {

	private static Extractor _instance = null;
	
	private Extractor(){}
	
	/**
	 * Liefert Instanz des Managers
	 * 
	 * @return DB2ConnectionManager
	 */
	public static Extractor getInstance() {
		if (_instance == null) {
			_instance = new Extractor();
		}
		return _instance;
	}
	
	/**
	 * Liefert Resultset des Article Join zurück
	 * @return
	 */
	public ResultSet extractArticle() {

		System.out.print("Extract Articles gestartet...");
		
		ResultSet rs = null;
		
		try {
			
			Connection con = DB2ConnectionManager.getInstance().getConnection();
			
			String selectSQL = "SELECT a.articleid, a.name, a.preis, g.name as productgroup, f.name as productfamily, c.name as productcategory " +
								" FROM DB2INST1.articleid as a, DB2INST1.productgroupid as g, DB2INST1.productfamilyid as f, DB2INST1.productcategoryid as c " +
								" WHERE a.productgroupid = g.productgroupid AND " +
									"g.productfamilyid = f.productfamilyid AND " +  
									"f.productcategoryid = c.productcategoryid";
			
			PreparedStatement pstmt = con.prepareStatement(selectSQL);
			
			rs = pstmt.executeQuery();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		System.out.println("beendet!");
		
		return rs;
	}
	
	/**
	 * Liefert Resultset des Shop Join zurück
	 * @return
	 */
	public ResultSet extractShop() {
		
		System.out.print("Extract Shops gestartet...");
		
		ResultSet rs = null;
		
		try {
			
			Connection con = DB2ConnectionManager.getInstance().getConnection();
			
			String selectSQL = "SELECT sh.shopid, sh.name, st.name as stadt, r.name as region, l.name as land "+   
								" FROM DB2INST1.shopid as sh, DB2INST1.stadtid as st, DB2INST1.regionid as r, DB2INST1.landid as l "+
								" WHERE sh.stadtid = st.stadtid AND "+
								" st.regionid = r.regionid AND "+
								" r.landid = l.landid";
			
			PreparedStatement pstmt = con.prepareStatement(selectSQL);
			
			rs = pstmt.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		System.out.println("beendet!");
		
		return rs;
	}
	
	/**
	 * Liefert Liste der Verkaufsdaten aus der sales.csv Dateizurück
	 * @return
	 */
	public Collection<String[]> extractSales() {
		
		System.out.print("Extract Sales gestartet...");
		
		Collection<String[]> result = new ArrayList<String[]>();
		
		try {
			
			File x = new File("data/sales.csv");
			FileInputStream fis = new FileInputStream(x);
			InputStreamReader isr = new InputStreamReader(fis, Charset.forName("ISO-8859-1"));
			BufferedReader br = new BufferedReader(isr);

			String columnLine = br.readLine();
			
			String fileLine;
			
			 while( (fileLine = br.readLine()) != null ) { 
				 
				 result.add(fileLine.split(";"));
			 }
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		System.out.println("beendet!");

		return result;
	}
	
	
}
